package com.dao.impl;

import com.bean.Express;
import com.dao.BaseExpressDao;
import com.exception.DuplicateCodeException;
import com.util.DBUtils;

import java.sql.SQLException;
import java.util.*;

public class ExpressDapMysql extends DBUtils implements BaseExpressDao {
    private static final String SQL_CONSOLE = "SELECT " +
            "COUNT(id) data1_size," +
            "COUNT(TO_DAYS(intime)=TO_DAYS(NOW()) OR NULL) data1_day," +
            "COUNT(status=0 OR NULL) data2_size," +
            "COUNT(TO_DAYS(intime)=TO_DAYS(NOW()) AND status=0 OR NULL) data2_day " +
            "FROM express";
    private static final String SQL_FIND_LIMIT = "SELECT * FROM express LIMIT ?,?";
    private static final String SQL_FIND_ALL = "SELECT * FROM express";
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM express WHERE number=?";
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM express WHERE code=?";
    private static final String SQL_FIND_BY_USER_PHONE = "SELECT * FROM express WHERE userphone=?";
    private static final String SQL_FIND_BY_SYS_PHONE = "SELECT * FROM express WHERE sysphone=?";
    private static final String SQL_INSERT = "INSERT INTO express (number,username,userphone,company,code,intime,status,sysphone) " +
            "VALUES(?,?,?,?,?,NOW(),0,?)";
    private static final String SQL_UPDATE = "UPDATE express SET number=?,username=?,company=?,status=? WHERE id=?";
    private static final String SQL_UPDATE_STATUS = "UPDATE express SET status=1,outtime=NOW(),code=NULL WHERE code=?";
    private static final String SQL_DELETE = "DELETE FROM express WHERE id=?";
    private static final String SQL_FIND_BY_USER_PHONE_AND_STATUS = "SELECT * FROM express WHERE userphone=? AND status=?";

    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String, Integer>> data = new ArrayList<>();
        resultSet = query(SQL_CONSOLE, null);
        try {
            if (resultSet.next()) {
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size", data1_size);
                data1.put("data1_day", data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size", data2_size);
                data2.put("data2_day", data2_day);
                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Express> data = new ArrayList<>();
        if (limit) {
            ArrayList arrayList = new ArrayList();
            arrayList.add(offset);
            arrayList.add(pageNumber);
            resultSet = query(SQL_FIND_LIMIT, arrayList);
        } else {
            resultSet = query(SQL_FIND_ALL, null);
        }
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public Express findByNumber(String number) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(number);
        resultSet = query(SQL_FIND_BY_NUMBER, arrayList);
        Express express = new Express();
        try {
            if (resultSet.next()) {
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
            } else {
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return express;
    }

    @Override
    public Express findByCode(String code) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(code);
        resultSet = query(SQL_FIND_BY_CODE, arrayList);
        Express express = new Express();
        try {
            if (resultSet.next()) {
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return express;
    }

    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> data = new ArrayList<>();
        ArrayList arrayList = new ArrayList();
        arrayList.add(userPhone);
        resultSet = query(SQL_FIND_BY_USER_PHONE, arrayList);
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        ArrayList<Express> data = new ArrayList<>();
        ArrayList arrayList = new ArrayList();
        arrayList.add(userPhone);
        arrayList.add(status);
        resultSet = query(SQL_FIND_BY_USER_PHONE_AND_STATUS, arrayList);
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        ArrayList arrayList = new ArrayList();
        arrayList.add(sysPhone);
        resultSet = query(SQL_FIND_BY_SYS_PHONE, arrayList);
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                express.setStatus(resultSet.getInt("status"));
                express.setSysPhone(resultSet.getString("sysphone"));
                data.add(express);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return data;
    }

    @Override
    public boolean insert(Express e) throws DuplicateCodeException {
        ArrayList arrayList = new ArrayList();
        arrayList.add(e.getNumber());
        arrayList.add(e.getUserName());
        arrayList.add(e.getUserPhone());
        arrayList.add(e.getCompany());
        arrayList.add(e.getCode());
        arrayList.add(e.getSysPhone());
        int i = 0;
        try {
            i = update(SQL_INSERT, arrayList);
        } catch (Exception exception) {
            if (exception.getMessage().endsWith("for key 'express.code'")) {
                DuplicateCodeException duplicateCodeException = new DuplicateCodeException(exception.getMessage());
                throw duplicateCodeException;
            } else {
                exception.printStackTrace();
            }
        } finally {
            closeAll();
        }
        if (i > 0)
            return true;
        else
            return false;
    }

    @Override
    public boolean update(int id, Express e) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(e.getNumber());
        arrayList.add(e.getUserName());
        arrayList.add(e.getCompany());
        arrayList.add(e.getStatus());
        arrayList.add(id);
        int i = 0;
        try {
            i = update(SQL_UPDATE, arrayList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        if (i > 0)
            return true;
        else
            return false;
    }

    @Override
    public boolean updateStatus(String code) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(code);
        int i = 0;
        try {
            i = update(SQL_UPDATE_STATUS, arrayList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        if (i > 0)
            return true;
        else
            return false;
    }

    @Override
    public boolean delete(int id) {
        ArrayList arrayList = new ArrayList();
        arrayList.add(id);
        int i = 0;
        try {
            i = update(SQL_DELETE, arrayList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if (i > 0)
            return true;
        else
            return false;
    }
}
